﻿Imports System.Data
Imports System.IO
Imports log4net

Partial Class ImportFrm
    Inherits System.Web.UI.Page

    Public AlertText As String
    Private Shared logger As ILog = LogManager.GetLogger("ImportFrm")

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        'Session("PlanYear") = "2557"
        If Session("users") Is Nothing Then
            Response.Redirect("LoginForm.aspx")
        End If
        
        If IsPostBack = False Then
            PlanYearsTxt.Text = Session("PlanYear")
            Label1.Text = "ระบบจัดทำงบประมาณ > จัดทำงบประมาณ > Import"
            FromVersionTxt.Text = "0"
        End If

        Server.ScriptTimeout = 3600
    End Sub

    Protected Sub WebCombo1_InitializeDataSource(sender As Object, e As ISNet.WebUI.WebCombo.DataSourceEventArgs)
        Dim ds As New DataSet
        Dim dt As DataTable = ds.Tables.Add("Items")
        dt.Columns.Add("Code", Type.GetType("System.String"))
        dt.Columns.Add("Description", Type.GetType("System.String"))
        Dim r As DataRow
        r = dt.NewRow()
        r("Code") = "1"
        r("Description") = "ข้อมูล Actual จากระบบ FM"
        dt.Rows.Add(r)

        r = dt.NewRow()
        r("Code") = "2"
        r("Description") = "ข้อมูล Actual จากระบบ GL (เบิกจ่าย, Product, รายได้)"
        dt.Rows.Add(r)

        r = dt.NewRow()
        r("Code") = "3"
        r("Description") = "ข้อมูล งบสุทธิ"
        dt.Rows.Add(r)

        r = dt.NewRow()
        r("Code") = "4"
        r("Description") = "ข้อมูล HR"
        dt.Rows.Add(r)

        e.DataSource = dt
    End Sub

    Protected Sub SearhBtn_Click(sender As Object, e As ImageClickEventArgs) Handles SearhBtn.Click
        Dim starttime, endtime As Date
        starttime = Now
        Dim bd_adapter As New MassChangeDSTableAdapters.BudgetDetailsTableAdapter
        Dim bd12_adapter As New MassChangeDSTableAdapters.Budget12TableAdapter
        Dim ex_adapter As New ExportTableAdapters.BudgetDetailsTableAdapter
        Dim Extension As String = ""
        Dim FileName As String = ""
        Dim Filedata As String
        Dim error_txt As String = ""
        If FileUpload1.HasFile Then
            Try
                'FileUpload1.SaveAs(Server.MapPath("~/Temp/") & FileUpload1.FileName)
                Extension = System.IO.Path.GetExtension(FileUpload1.FileName)
                Dim locationStart As Integer = InStr(FileUpload1.FileName, "BB_")
                Dim txtyear = Mid(FileUpload1.FileName, locationStart + 3, 4)

                'check file name year by yui
                'If chkFileName(FileUpload1.FileName) Then
                If PlanYearsTxt.Text = txtyear Then
                    FileName = GenerateFileName("yyyyMMddhhmmss") + Extension
                    'FileUpload1.SaveAs(Server.MapPath("~/Temp/") & FileName)

                    Dim b As New BinaryReader(FileUpload1.FileContent)
                    Dim binData As Byte() = b.ReadBytes(FileUpload1.FileContent.Length)
                    b.Dispose()
                    Filedata = System.Text.Encoding.UTF8.GetString(binData)
                Else
                    Throw New Exception("Master Error")

                End If
            Catch ex As Exception
                Alert("ชื่อไฟล์ไม่ตรงกับปีงบประมาณ")
                Exit Sub
            End Try
        Else
            Alert("กรุณาระบุไฟล์")
            Exit Sub
        End If

        'Dim SReadLine As Stream

        'SReadLine = File.OpenRead(FileName)
        'Dim SrReadLine As StreamReader = New StreamReader(SReadLine, System.Text.Encoding.Default)
        'SrReadLine.BaseStream.Seek(0, SeekOrigin.Begin)
        'Dim Filedata As String = SrReadLine.ReadToEnd()
        'SrReadLine.Close()
        Dim delimeter As String
        If InStr(Filedata, "|") > 0 Then
            delimeter = "|"
        Else
            delimeter = Chr(9) 'Tab
        End If
        Dim streader As New StringReader(Filedata)
        'Dim FileArray() As String = Split(Filedata, Chr(13) & Chr(10))
        'Dim TotalLine As Integer = FileArray.Length()
        Dim TotalLine As Integer = 0
        Dim DataArray() As String
        Dim planyear As Integer
        Dim version As String
        Dim fct_code, cmmt_code, FundedProgram, FunctionalArea, product_code, activity_type_code, fund_code As String
        'Dim amount As Decimal
        ' Dim data1, data2, data3 As Decimal
        ' Dim amount1, amount2, amount3, amount4, amount5, amount6, amount7, amount8, amount9, amount10, amount11, amount12 As Decimal
        Dim period As Integer
        ' Dim sql As String
        Dim exist, master_exist As Integer

        planyear = Val(PlanYearsTxt.Text)
        version = FromVersionTxt.Text
        Dim oTrans = bd_adapter.CreateTransaction()
        Dim line As String = streader.ReadLine()
        Dim linecount As Integer = 0


        While line <> Nothing

            linecount += 1
            'If linecount = 1 And delimeter <> "|" Then
            '    Continue While
            'End If
            DataArray = Split(line, delimeter)

            If DataArray.Length > 2 Then
                If WebCombo1.Value = "1" Then 'Actual FM
                    planyear = PlanYearsTxt.Text
                    fct_code = Right(DataArray(2), 4)
                    cmmt_code = Right(DataArray(3).Trim, 8)
                    FundedProgram = ""
                    FunctionalArea = DataArray(5)
                    activity_type_code = "NULL"
                    product_code = "NULL"
                    If Len(FunctionalArea) = 8 Then
                        product_code = FunctionalArea
                    ElseIf Len(FunctionalArea) = 6 Then
                        activity_type_code = FunctionalArea
                    End If
                    FunctionalArea = ""
                    period = Val(DataArray(6))

                ElseIf WebCombo1.Value = "2" Then 'Actual GL
                    planyear = Val(PlanYearsTxt.Text)
                    FundedProgram = ""
                    cmmt_code = Right(DataArray(1).Trim, 8)
                    fct_code = Right(DataArray(3).Trim, 4)
                    FunctionalArea = DataArray(4).Trim
                    activity_type_code = "NULL"
                    product_code = "NULL"
                    If Len(FunctionalArea) = 8 Then
                        product_code = FunctionalArea
                    ElseIf Len(FunctionalArea) = 6 Then
                        activity_type_code = FunctionalArea
                    End If

                ElseIf WebCombo1.Value = "3" Then 'Budget
                    planyear = PlanYearsTxt.Text
                    FundedProgram = ""
                    fund_code = DataArray(1).Trim
                    fct_code = Right(DataArray(2).Trim, 4)
                    If fct_code.Length < 4 Then
                        fct_code = SpacePrefix(fct_code, 4)
                    End If
                    cmmt_code = Right(DataArray(3).Trim, 8)
                    FundedProgram = DataArray(4).Trim
                    If FundedProgram = "OPEX" Then
                        FundedProgram = ""
                    End If

                    FunctionalArea = DataArray(5).Trim
                    activity_type_code = "NULL"
                    product_code = "NULL"
                    If Len(FunctionalArea) = 8 Then
                        product_code = FunctionalArea
                    ElseIf Len(FunctionalArea) = 6 Then
                        activity_type_code = FunctionalArea
                    End If

                ElseIf WebCombo1.Value = "4" Then 'HR
                    planyear = PlanYearsTxt.Text
                    FundedProgram = ""
                    fund_code = DataArray(1).Trim
                    fct_code = Right(DataArray(2).Trim, 4)
                    cmmt_code = Right(DataArray(3).Trim, 8)
                    FundedProgram = DataArray(4).Trim
                    If FundedProgram = "OPEX" Then
                        FundedProgram = ""
                    End If

                    FunctionalArea = DataArray(5).Trim
                    activity_type_code = "NULL"
                    product_code = "NULL"
                    If Len(FunctionalArea) = 8 Then
                        product_code = FunctionalArea
                    ElseIf Len(FunctionalArea) = 6 Then
                        activity_type_code = FunctionalArea
                    End If

                End If

                Try
                    master_exist = ex_adapter.CheckFct(fct_code, Val(PlanYearsTxt.Text))
                    If master_exist = 0 Then
                        If CheckBox1.Checked = True Then
                            InsertFct(fct_code, planyear)
                        Else
                            error_txt = error_txt & "พบปัญหาบรรทัดที่ " & linecount & " Fund Center :" & fct_code & " ไม่มีอยู่ใน Master " & vbCrLf
                            ' Alert("พบปัญหาบรรทัดที่ " & linecount & " Fund Center :" & fct_code & " ไม่มีอยู่ใน Master ")
                            ' Throw New ArgumentException("Master Error")
                            logger.Error(line.ToString)
                        End If
                    End If

                    If activity_type_code <> "NULL" Then
                        master_exist = ex_adapter.CheckActType(activity_type_code, Val(PlanYearsTxt.Text))
                        If master_exist = 0 Then
                            If CheckBox1.Checked = True Then
                                InsertActivityType(activity_type_code, planyear)
                            Else
                                ' Alert("พบปัญหาบรรทัดที่ " & linecount & " Activity Type :" & activity_type_code & " ไม่มีอยู่ใน Master ")
                                'Throw New ArgumentException("Master Error")
                                error_txt = error_txt & "พบปัญหาบรรทัดที่ " & linecount & " Activity Type :" & activity_type_code & " ไม่มีอยู่ใน Master " & vbCrLf
                                logger.Error(line.ToString)
                            End If
                        End If
                        activity_type_code = "'" & activity_type_code & "'"
                    End If

                    If product_code <> "NULL" Then
                        master_exist = ex_adapter.CheckProduct(product_code, Val(PlanYearsTxt.Text))
                        If master_exist = 0 Then
                            If CheckBox1.Checked = True Then
                                InsertProduct(product_code, planyear)
                            Else
                                ' Alert("พบปัญหาบรรทัดที่ " & linecount & " Product:" & product_code & " ไม่มีอยู่ใน Master ")
                                'Throw New ArgumentException("Master Error")
                                error_txt = error_txt & "พบปัญหาบรรทัดที่ " & linecount & " Product:" & product_code & " ไม่มีอยู่ใน Master " & vbCrLf
                                logger.Error(line.ToString)
                            End If
                        End If
                        product_code = "'" & product_code & "'"
                    End If

                    master_exist = ex_adapter.CheckCmmt(cmmt_code, Val(PlanYearsTxt.Text))
                    If master_exist = 0 Then
                        If CheckBox1.Checked = True Then
                            InsertCmmt(cmmt_code, planyear)
                        Else
                            ' Alert("พบปัญหาบรรทัดที่ " & linecount & " Commitment: " & cmmt_code & " ไม่มีอยู่ใน Master ")
                            ' Throw New ArgumentException("Master Error")
                            error_txt = error_txt & "พบปัญหาบรรทัดที่ " & linecount & " Commitment: " & cmmt_code & " ไม่มีอยู่ใน Master " & vbCrLf
                            logger.Error(line.ToString)
                        End If
                    End If
                Catch ex As Exception
                    oTrans.Rollback()

                    logger.Error(line.ToString)
                    logger.Error(ex.Message)
                    logger.Error(ex.StackTrace)

                    Alert("มีข้อผิดพลาดในการนำเข้าข้อมูล" + " " + ex.Message)
                    Exit Sub
                End Try
            End If
            line = streader.ReadLine()
        End While

        'Save Data
        Dim linecountS2 As Integer = 0


        Try
            If error_txt <> "" Then
                hd_err_txt.Value = error_txt
                Button1.Visible = True
                Throw New Exception(" ")
            Else
                Button1.Visible = False
                Dim delimeterS2 As String
                If InStr(Filedata, "|") > 0 Then
                    delimeterS2 = "|"
                Else
                    delimeterS2 = Chr(9)
                End If

                Dim streaderS2 As New StringReader(Filedata)
                Dim TotalLineS2 As Integer = 0
                Dim DataArrayS2() As String
                Dim planyearS2 As Integer
                Dim versionS2 As String
                Dim fct_codeS2, cmmt_codeS2, FundedProgramS2, FunctionalAreaS2, product_codeS2, activity_type_codeS2, fund_codeS2 As String
                Dim amountS2 As Decimal
                Dim data1S2, data2S2, data3S2 As Decimal
                Dim amount1S2, amount2S2, amount3S2, amount4S2, amount5S2, amount6S2, amount7S2, amount8S2, amount9S2, amount10S2, amount11S2, amount12S2 As Decimal
                Dim periodS2 As Integer
                Dim sqlS2, SqlB12 As String
                Dim sqlBudget12 As String
                Dim existS2, master_existS2 As Integer
                Dim username As String = Session("users")

                planyearS2 = Val(PlanYearsTxt.Text)
                versionS2 = FromVersionTxt.Text

                'For idx = 0 To TotalLine - 1
                Dim lineS2 As String = streaderS2.ReadLine()

                Dim budgetDetailTA As New MassChangeDSTableAdapters.BudgetDetailsTableAdapter
                ' Increase time-out to 60 seconds

                'ทำงานเมื่อไม่มี error
                If error_txt = "" Then
                    While (lineS2 <> Nothing)

                        linecountS2 += 1
                        'If linecountS2 = 1 And delimeterS2 <> "|" Then
                        '    Continue While
                        'End If

                        DataArrayS2 = Split(lineS2, delimeterS2)

                        If DataArrayS2.Length > 2 Then
                            Try
                                If WebCombo1.Value = "1" Then 'Actual FM
                                    planyearS2 = PlanYearsTxt.Text
                                    fct_codeS2 = Right(DataArrayS2(2), 4)
                                    cmmt_codeS2 = Right(DataArrayS2(3).Trim, 8)
                                    FundedProgramS2 = ""
                                    FunctionalAreaS2 = DataArrayS2(5)
                                    activity_type_codeS2 = "NULL"
                                    product_codeS2 = "NULL"
                                    If Len(FunctionalAreaS2) = 8 Then
                                        product_codeS2 = FunctionalAreaS2
                                    ElseIf Len(FunctionalAreaS2) = 6 Then
                                        activity_type_codeS2 = FunctionalAreaS2
                                    Else
                                        logger.Info("User " & Session("users") & " Functional Area Is " & FunctionalAreaS2)
                                        Alert("Functional Area is not correct (" & FunctionalAreaS2 & ")")
                                    End If

                                    FunctionalAreaS2 = ""
                                    periodS2 = Val(DataArrayS2(6))
                                    data1S2 = Val(DataArrayS2(7))
                                    amountS2 = Val(Replace(DataArrayS2(8), ",", ""))


                                    Dim totalAmount As Decimal = amountS2

                                    sqlS2 = "insert into budgetdetails (planyear , baseyear , version, FundCenter_FundCenterCode,FundCenter_PlanYear,Cmmt_CmmtCode,Cmmt_PlanYear,FundedProgram,FunctionalArea,Product_ProductCode,Product_PlanYear,ActivityType_ActivityTypeCode,ActivityType_PlanYear,Element3,Actual" & period & ",CreatedDate,OldAssetNo, Amount)"
                                    sqlS2 += " values ( " & planyearS2 & "," & planyearS2 & ",'" & versionS2 & "','" & fct_codeS2 & "'," & planyearS2 & ",'" & cmmt_codeS2 & "'," & planyearS2 & ",'" & FundedProgramS2 & "','" & FunctionalAreaS2 & "'," & IIf(product_codeS2 = "NULL", "NULL", "'" & product_codeS2 & "'") & "," & IIf(product_codeS2 = "NULL", "NULL", planyearS2) & "," & IIf(activity_type_codeS2 = "NULL", "NULL", "'" & activity_type_codeS2 & "'") & "," & IIf(activity_type_codeS2 = "NULL", "NULL", planyearS2) & "," & data1S2 & "," & amountS2 & ",GETDATE()," & "''" & "," & totalAmount & ")"
                                    sqlS2 += "; SELECT SCOPE_IDENTITY();"
                                    budgetDetailTA.SetCommandTimeOut(ConfigurationManager.AppSettings("QueryTimeOut"))
                                    'bd_adapter.Execute(sqlS2)
                                    Dim BudgetDetail_Id = bd_adapter.Execute1(sqlS2)
                                    sqlBudget12 = "insert into Budget12 (planyear , baseyear , version, FundCenter_FundCenterCode,FundCenter_PlanYear,Cmmt_CmmtCode,Cmmt_PlanYear,FundedProgram,FunctionalArea,Product_ProductCode,Product_PlanYear,ActivityType_ActivityTypeCode,ActivityType_PlanYear,Amount1,Amount2,Amount3,Amount4,Amount5,Amount6,Amount7,Amount8,Amount9,Amount10,Amount11,Amount12,CreatedDate, CreatedBy, BudgetDetail_Id)"
                                    sqlBudget12 += " values ( " & planyearS2 & "," & planyearS2 & ",'" & versionS2 & "','" & fct_codeS2 & "'," & planyearS2 & ",'" & cmmt_codeS2 & "'," & planyearS2 & ",'" & FundedProgramS2 & "','" & FunctionalAreaS2 & "'," & IIf(product_codeS2 = "NULL", "NULL", "'" & product_codeS2 & "'") & "," & IIf(product_codeS2 = "NULL", "NULL", planyearS2) & "," & IIf(activity_type_codeS2 = "NULL", "NULL", "'" & activity_type_codeS2 & "'") & "," & IIf(activity_type_codeS2 = "NULL", "NULL", planyearS2) & "," & amount1S2 & "," & amount2S2 & "," & amount3S2 & "," & amount4S2 & "," & amount5S2 & "," & amount6S2 & "," & amount7S2 & "," & amount8S2 & "," & amount9S2 & "," & amount10S2 & "," & amount11S2 & "," & amount12S2 & ",GETDATE(), '" & username & "', " & BudgetDetail_Id & ")"
                                    Dim r As Integer = bd12_adapter.Execute(sqlBudget12)

                                    logger.Info("User " & Session("users") & " import actual from FM with total amount " & linecountS2.ToString)

                                ElseIf WebCombo1.Value = "2" Then 'Actual GL
                                    planyearS2 = Val(PlanYearsTxt.Text)
                                    FundedProgramS2 = ""
                                    cmmt_codeS2 = Right(DataArrayS2(1).Trim, 8)
                                    If DataArrayS2(2) = "H" Then
                                        data1S2 = 0
                                    Else
                                        data1S2 = 1
                                    End If

                                    fct_codeS2 = Right(DataArrayS2(3).Trim, 4)
                                    FunctionalAreaS2 = DataArrayS2(4).Trim
                                    activity_type_codeS2 = "NULL"
                                    product_codeS2 = "NULL"
                                    If Len(FunctionalAreaS2) = 8 Then
                                        product_codeS2 = FunctionalAreaS2
                                    ElseIf Len(FunctionalAreaS2) = 6 Then
                                        activity_type_codeS2 = FunctionalAreaS2
                                    End If
                                    FunctionalAreaS2 = ""
                                    data2S2 = Val(Replace(DataArrayS2(5).Trim, ",", ""))
                                    amount1S2 = Val(Replace(DataArrayS2(6).Trim, ",", ""))
                                    amount2S2 = Val(Replace(DataArrayS2(7).Trim, ",", ""))
                                    amount3S2 = Val(Replace(DataArrayS2(8).Trim, ",", ""))
                                    amount4S2 = Val(Replace(DataArrayS2(9).Trim, ",", ""))
                                    amount5S2 = Val(Replace(DataArrayS2(10).Trim, ",", ""))
                                    amount6S2 = Val(Replace(DataArrayS2(11).Trim, ",", ""))
                                    amount7S2 = Val(Replace(DataArrayS2(12).Trim, ",", ""))
                                    amount8S2 = Val(Replace(DataArrayS2(13).Trim, ",", ""))
                                    amount9S2 = Val(Replace(DataArrayS2(14).Trim, ",", ""))
                                    amount10S2 = Val(Replace(DataArrayS2(15).Trim, ",", ""))
                                    amount11S2 = Val(Replace(DataArrayS2(16).Trim, ",", ""))
                                    amount12S2 = Val(Replace(DataArrayS2(17).Trim, ",", ""))
                                    Dim totalAmount As Decimal = amount1S2 + amount2S2 + amount3S2 + amount4S2 + amount5S2 + amount6S2 + amount7S2 + amount8S2 + amount9S2 + amount10S2 + amount11S2 + amount12S2
                                    sqlS2 = "insert into budgetdetails (planyear , baseyear , version, FundCenter_FundCenterCode,FundCenter_PlanYear,Cmmt_CmmtCode,Cmmt_PlanYear,FundedProgram,FunctionalArea,Product_ProductCode,Product_PlanYear,ActivityType_ActivityTypeCode,ActivityType_PlanYear,Element1,Element2,Actual1,Actual2,Actual3,Actual4,Actual5,Actual6,Actual7,Actual8,Actual9,Actual10,Actual11,Actual12,CreatedDate,OldAssetNo, Amount)"
                                    sqlS2 += " values ( " & planyearS2 & "," & planyearS2 & ",'" & versionS2 & "','" & fct_codeS2 & "'," & planyearS2 & ",'" & cmmt_codeS2 & "'," & planyearS2 & ",'" & FundedProgramS2 & "','" & FunctionalAreaS2 & "'," & IIf(product_codeS2 = "NULL", "NULL", "'" & product_codeS2 & "'") & "," & IIf(product_codeS2 = "NULL", "NULL", planyearS2) & "," & IIf(activity_type_codeS2 = "NULL", "NULL", "'" & activity_type_codeS2 & "'") & "," & IIf(activity_type_codeS2 = "NULL", "NULL", planyearS2) & "," & data1S2 & "," & data2S2 & "," & amount1S2 & "," & amount2S2 & "," & amount3S2 & "," & amount4S2 & "," & amount5S2 & "," & amount6S2 & "," & amount7S2 & "," & amount8S2 & "," & amount9S2 & "," & amount10S2 & "," & amount11S2 & "," & amount12S2 & ",GETDATE()," & "''" & "," & totalAmount & ")"
                                    sqlS2 += "; SELECT SCOPE_IDENTITY();"
                                    budgetDetailTA.SetCommandTimeOut(ConfigurationManager.AppSettings("QueryTimeOut"))
                                    'Dim a = bd_adapter.Execute(sqlS2)
                                    Dim BudgetDetail_Id = bd_adapter.Execute1(sqlS2)
                                    sqlBudget12 = "insert into Budget12 (planyear , baseyear , version, FundCenter_FundCenterCode,FundCenter_PlanYear,Cmmt_CmmtCode,Cmmt_PlanYear,FundedProgram,FunctionalArea,Product_ProductCode,Product_PlanYear,ActivityType_ActivityTypeCode,ActivityType_PlanYear,Amount1,Amount2,Amount3,Amount4,Amount5,Amount6,Amount7,Amount8,Amount9,Amount10,Amount11,Amount12,CreatedDate, CreatedBy, BudgetDetail_Id)"
                                    sqlBudget12 += " values ( " & planyearS2 & "," & planyearS2 & ",'" & versionS2 & "','" & fct_codeS2 & "'," & planyearS2 & ",'" & cmmt_codeS2 & "'," & planyearS2 & ",'" & FundedProgramS2 & "','" & FunctionalAreaS2 & "'," & IIf(product_codeS2 = "NULL", "NULL", "'" & product_codeS2 & "'") & "," & IIf(product_codeS2 = "NULL", "NULL", planyearS2) & "," & IIf(activity_type_codeS2 = "NULL", "NULL", "'" & activity_type_codeS2 & "'") & "," & IIf(activity_type_codeS2 = "NULL", "NULL", planyearS2) & "," & amount1S2 & "," & amount2S2 & "," & amount3S2 & "," & amount4S2 & "," & amount5S2 & "," & amount6S2 & "," & amount7S2 & "," & amount8S2 & "," & amount9S2 & "," & amount10S2 & "," & amount11S2 & "," & amount12S2 & ",GETDATE(), '" & username & "', " & BudgetDetail_Id & ")"
                                    Dim r As Integer = bd12_adapter.Execute(sqlBudget12)
                                    logger.Info("User " & Session("users") & " import actual from non-FM with total amount " & linecountS2.ToString)

                                ElseIf WebCombo1.Value = "3" Then ' Budget
                                    Dim BD_planyear, BD_baseyear, BD_FundCenter_PlanYear, BD_Cmmt_PlanYear, BD_Product_PlanYear, BD_ActivityType_PlanYear As Int16
                                    Dim BD_version, BD_FundCenter_FundCenterCode, BD_Cmmt_CmmtCode, BD_FundedProgram, BD_FunctionalArea, BD_Product_ProductCode, BD_ActivityType_ActivityTypeCode, BD_CreatedDate, BD_OldAssetNo As String
                                    Dim BD_Actual1, BD_Actual2, BD_Actual3, BD_Actual4, BD_Actual5, BD_Actual6, BD_Actual7, BD_Actual8, BD_Actual9, BD_Actual10, BD_Actual11, BD_Actual12, BD_TotalAmount As Decimal
                                    BD_planyear = PlanYearsTxt.Text
                                    BD_baseyear = PlanYearsTxt.Text
                                    BD_FundCenter_PlanYear = PlanYearsTxt.Text
                                    BD_Cmmt_PlanYear = PlanYearsTxt.Text
                                    BD_Product_PlanYear = PlanYearsTxt.Text
                                    BD_ActivityType_PlanYear = PlanYearsTxt.Text
                                    BD_version = versionS2

                                    fct_codeS2 = Right(DataArrayS2(2).Trim, 4)
                                    If fct_codeS2.Length < 4 Then
                                        fct_codeS2 = SpacePrefix(fct_codeS2, 4)
                                    End If
                                    fund_codeS2 = DataArrayS2(1).Trim
                                    BD_FundCenter_FundCenterCode = fct_codeS2
                                    BD_Cmmt_CmmtCode = Right(DataArrayS2(3).Trim, 8)

                                    FundedProgramS2 = Nothing

                                    FundedProgramS2 = DataArrayS2(4).Trim
                                    If FundedProgramS2 = "OPEX" Then
                                        FundedProgramS2 = Nothing
                                    End If
                                    BD_FundedProgram = FundedProgramS2

                                    BD_FunctionalArea = DataArrayS2(5).Trim

                                    BD_OldAssetNo = Nothing

                                    BD_ActivityType_ActivityTypeCode = Nothing
                                    BD_Product_ProductCode = Nothing
                                    If Len(BD_FunctionalArea) = 8 Then
                                        BD_Product_ProductCode = BD_FunctionalArea
                                    ElseIf Len(BD_FunctionalArea) = 6 Then
                                        BD_ActivityType_ActivityTypeCode = BD_FunctionalArea
                                    End If
                                    BD_Product_ProductCode = IIf(BD_Product_ProductCode = "NULL", Nothing, BD_Product_ProductCode)
                                    BD_Product_PlanYear = IIf(BD_Product_ProductCode = "NULL", Nothing, BD_Product_PlanYear)
                                    BD_ActivityType_ActivityTypeCode = IIf(BD_ActivityType_ActivityTypeCode = "NULL", Nothing, BD_ActivityType_ActivityTypeCode)
                                    BD_ActivityType_PlanYear = IIf(BD_ActivityType_ActivityTypeCode = "NULL", Nothing, BD_ActivityType_PlanYear)

                                    BD_FunctionalArea = Nothing
                                    BD_Actual1 = Val(Replace(DataArrayS2(6).Trim, ",", ""))
                                    BD_Actual2 = Val(Replace(DataArrayS2(7).Trim, ",", ""))
                                    BD_Actual3 = Val(Replace(DataArrayS2(8).Trim, ",", ""))
                                    BD_Actual4 = Val(Replace(DataArrayS2(9).Trim, ",", ""))
                                    BD_Actual5 = Val(Replace(DataArrayS2(10).Trim, ",", ""))
                                    BD_Actual6 = Val(Replace(DataArrayS2(11).Trim, ",", ""))
                                    BD_Actual7 = Val(Replace(DataArrayS2(12).Trim, ",", ""))
                                    BD_Actual8 = Val(Replace(DataArrayS2(13).Trim, ",", ""))
                                    BD_Actual9 = Val(Replace(DataArrayS2(14).Trim, ",", ""))
                                    BD_Actual10 = Val(Replace(DataArrayS2(15).Trim, ",", ""))
                                    BD_Actual11 = Val(Replace(DataArrayS2(16).Trim, ",", ""))
                                    BD_Actual12 = Val(Replace(DataArrayS2(17).Trim, ",", ""))

                                    BD_CreatedDate = Now.ToString()

                                    BD_TotalAmount = BD_Actual1 + BD_Actual2 + BD_Actual3 + BD_Actual4 + BD_Actual5 + BD_Actual6 + BD_Actual7 + BD_Actual8 + BD_Actual9 + BD_Actual10 + BD_Actual11 + BD_Actual12
                                    'sqlS2 = "insert into budgetdetails (planyear , baseyear , version, FundCenter_FundCenterCode,FundCenter_PlanYear,Cmmt_CmmtCode,Cmmt_PlanYear,FundedProgram,FunctionalArea,Product_ProductCode,Product_PlanYear,ActivityType_ActivityTypeCode,ActivityType_PlanYear,Actual1,Actual2,Actual3,Actual4,Actual5,Actual6,Actual7,Actual8,Actual9,Actual10,Actual11,Actual12,CreatedDate,OldAssetNo, Amount)"
                                    'sqlS2 += " values ( " & planyearS2 & "," & planyearS2 & ",'" & versionS2 & "','" & fct_codeS2 & "'," & planyearS2 & ",'" & cmmt_codeS2 & "'," & planyearS2 & ",'" & FundedProgramS2 & "','" & FunctionalAreaS2 & "'," & IIf(product_codeS2 = "NULL", "NULL", "'" & product_codeS2 & "'") & "," & IIf(product_codeS2 = "NULL", "NULL", planyearS2) & "," & IIf(activity_type_codeS2 = "NULL", "NULL", "'" & activity_type_codeS2 & "'") & "," & IIf(activity_type_codeS2 = "NULL", "NULL", planyearS2) & "," & amount1S2 & "," & amount2S2 & "," & amount3S2 & "," & amount4S2 & "," & amount5S2 & "," & amount6S2 & "," & amount7S2 & "," & amount8S2 & "," & amount9S2 & "," & amount10S2 & "," & amount11S2 & "," & amount12S2 & ",GETDATE()," & "''" & "," & totalAmount & " ) SELECT Id = SCOPE_IDENTITY() "
                                    'budgetDetailTA.SetCommandTimeOut(ConfigurationManager.AppSettings("QueryTimeOut"))
                                    'bd_adapter.Execute(sqlS2)

                                    Dim adapter As New BudgetDetailTableAdapters.BudgetDetailsTableAdapter
                                    Dim new_BudgetDetailID As Integer '= Convert.ToInt32(adapter.InsertReturnId(planyearS2, planyearS2, versionS2, fct_codeS2, planyearS2, cmmt_codeS2, planyearS2, FundedProgramS2, IIf(FunctionalAreaS2 = "NULL", "", FunctionalAreaS2), IIf(product_codeS2 = "NULL", "NULL", product_codeS2), IIf(product_codeS2 = "NULL", Convert.ToInt16(""), planyearS2), IIf(activity_type_codeS2 = "NULL", "NULL", activity_type_codeS2), IIf(activity_type_codeS2 = "NULL", Convert.ToInt16(""), planyearS2), amount1S2, amount2S2, amount3S2, amount4S2, amount5S2, amount6S2, amount7S2, amount8S2, amount9S2, amount10S2, amount11S2, amount12S2, Now.ToString(), "", totalAmount))
                                    new_BudgetDetailID = adapter.InsertReturnId(BD_planyear, BD_baseyear, BD_version, BD_FundCenter_FundCenterCode, BD_FundCenter_PlanYear, BD_Cmmt_CmmtCode, BD_Cmmt_PlanYear, BD_FundedProgram, BD_FunctionalArea, BD_Product_ProductCode, BD_Product_PlanYear, BD_ActivityType_ActivityTypeCode, BD_ActivityType_PlanYear, BD_Actual1, BD_Actual2, BD_Actual3, BD_Actual4, BD_Actual5, BD_Actual6, BD_Actual7, BD_Actual8, BD_Actual9, BD_Actual10, BD_Actual11, BD_Actual12, BD_CreatedDate, BD_OldAssetNo, BD_TotalAmount)
                                    sqlBudget12 = "insert into Budget12 (planyear , baseyear , version, FundCenter_FundCenterCode,FundCenter_PlanYear,Cmmt_CmmtCode,Cmmt_PlanYear,FundedProgram,FunctionalArea,Product_ProductCode,Product_PlanYear,ActivityType_ActivityTypeCode,ActivityType_PlanYear,Amount1,Amount2,Amount3,Amount4,Amount5,Amount6,Amount7,Amount8,Amount9,Amount10,Amount11,Amount12,CreatedDate, CreatedBy, BudgetDetail_Id)"
                                    sqlBudget12 += " values ( " & BD_planyear & "," & BD_baseyear & ",'" & BD_version & "','" & BD_FundCenter_FundCenterCode & "'," & BD_FundCenter_PlanYear & ",'" & BD_Cmmt_CmmtCode & "'," & BD_Cmmt_PlanYear & ",'" & BD_FundedProgram & "','" & BD_FunctionalArea & "'," & IIf(BD_Product_ProductCode = Nothing, "NULL", "'" & BD_Product_ProductCode & "'") & "," & IIf(BD_Product_PlanYear = Nothing, "NULL", BD_Product_PlanYear) & "," & IIf(BD_ActivityType_ActivityTypeCode = Nothing, "NULL", "'" & BD_ActivityType_ActivityTypeCode & "'") & "," & IIf(BD_ActivityType_PlanYear = Nothing, "NULL", BD_ActivityType_PlanYear) & "," & BD_Actual1 & "," & BD_Actual2 & "," & BD_Actual3 & "," & BD_Actual4 & "," & BD_Actual5 & "," & BD_Actual6 & "," & BD_Actual7 & "," & BD_Actual8 & "," & BD_Actual9 & "," & BD_Actual10 & "," & BD_Actual11 & "," & BD_Actual12 & ",GETDATE(), '" & username & "', " & new_BudgetDetailID & ")"
                                    Dim r As Integer = bd12_adapter.Execute(sqlBudget12)
                                    logger.Info("User " & Session("users") & " import actual from P&L with total amount " & linecountS2.ToString)
                                  
                                ElseIf WebCombo1.Value = "4" Then 'HR
                                    planyearS2 = PlanYearsTxt.Text
                                    FundedProgramS2 = ""
                                    fund_codeS2 = DataArrayS2(1).Trim
                                    fct_codeS2 = Right(DataArrayS2(2).Trim, 4)
                                    cmmt_codeS2 = Right(DataArrayS2(3).Trim, 8)
                                    FundedProgramS2 = DataArrayS2(4).Trim
                                    If FundedProgramS2 = "OPEX" Then
                                        FundedProgramS2 = ""
                                    End If

                                    FunctionalAreaS2 = DataArrayS2(5).Trim
                                    activity_type_codeS2 = "NULL"
                                    product_codeS2 = "NULL"
                                    If Len(FunctionalAreaS2) = 8 Then
                                        product_codeS2 = FunctionalAreaS2
                                    ElseIf Len(FunctionalAreaS2) = 6 Then
                                        activity_type_codeS2 = FunctionalAreaS2
                                    End If
                                    FunctionalAreaS2 = ""
                                    amount1S2 = Val(Replace(DataArrayS2(6).Trim, ",", ""))
                                    amount2S2 = Val(Replace(DataArrayS2(7).Trim, ",", ""))
                                    amount3S2 = Val(Replace(DataArrayS2(8).Trim, ",", ""))
                                    amount4S2 = Val(Replace(DataArrayS2(9).Trim, ",", ""))
                                    amount5S2 = Val(Replace(DataArrayS2(10).Trim, ",", ""))
                                    amount6S2 = Val(Replace(DataArrayS2(11).Trim, ",", ""))
                                    amount7S2 = Val(Replace(DataArrayS2(12).Trim, ",", ""))
                                    amount8S2 = Val(Replace(DataArrayS2(13).Trim, ",", ""))
                                    amount9S2 = Val(Replace(DataArrayS2(14).Trim, ",", ""))
                                    amount10S2 = Val(Replace(DataArrayS2(15).Trim, ",", ""))
                                    amount11S2 = Val(Replace(DataArrayS2(16).Trim, ",", ""))
                                    amount12S2 = Val(Replace(DataArrayS2(17).Trim, ",", ""))
                                    Dim totalAmount As Decimal = amount1S2 + amount2S2 + amount3S2 + amount4S2 + amount5S2 + amount6S2 + amount7S2 + amount8S2 + amount9S2 + amount10S2 + amount11S2 + amount12S2

                                    sqlS2 = "insert into budgetdetails (planyear , baseyear , version, FundCenter_FundCenterCode,FundCenter_PlanYear,Cmmt_CmmtCode,Cmmt_PlanYear,FundedProgram,FunctionalArea,Product_ProductCode,Product_PlanYear,ActivityType_ActivityTypeCode,ActivityType_PlanYear,Actual1,Actual2,Actual3,Actual4,Actual5,Actual6,Actual7,Actual8,Actual9,Actual10,Actual11,Actual12,CreatedDate,OldAssetNo, Amount)"
                                    sqlS2 += " values ( " & planyearS2 & "," & planyearS2 & ",'" & versionS2 & "','" & fct_codeS2 & "'," & planyearS2 & ",'" & cmmt_codeS2 & "'," & planyearS2 & ",'" & FundedProgramS2 & "','" & FunctionalAreaS2 & "'," & IIf(product_codeS2 = "NULL", "NULL", "'" & product_codeS2 & "'") & "," & IIf(product_codeS2 = "NULL", "NULL", planyearS2) & "," & IIf(activity_type_codeS2 = "NULL", "NULL", "'" & activity_type_codeS2 & "'") & "," & IIf(activity_type_codeS2 = "NULL", "NULL", planyearS2) & "," & amount1S2 & "," & amount2S2 & "," & amount3S2 & "," & amount4S2 & "," & amount5S2 & "," & amount6S2 & "," & amount7S2 & "," & amount8S2 & "," & amount9S2 & "," & amount10S2 & "," & amount11S2 & "," & amount12S2 & ",GETDATE()," & "''" & "," & totalAmount & ")"
                                    sqlS2 += "; SELECT SCOPE_IDENTITY();"
                                    'Response.Write(sql)
                                    budgetDetailTA.SetCommandTimeOut(ConfigurationManager.AppSettings("QueryTimeOut"))

                                    'bd_adapter.Execute(sqlS2)
                                    Dim BudgetDetail_Id = bd_adapter.Execute1(sqlS2)
                                    sqlBudget12 = "insert into Budget12 (planyear , baseyear , version, FundCenter_FundCenterCode,FundCenter_PlanYear,Cmmt_CmmtCode,Cmmt_PlanYear,FundedProgram,FunctionalArea,Product_ProductCode,Product_PlanYear,ActivityType_ActivityTypeCode,ActivityType_PlanYear,Amount1,Amount2,Amount3,Amount4,Amount5,Amount6,Amount7,Amount8,Amount9,Amount10,Amount11,Amount12,CreatedDate, CreatedBy, BudgetDetail_Id)"
                                    sqlBudget12 += " values ( " & planyearS2 & "," & planyearS2 & ",'" & versionS2 & "','" & fct_codeS2 & "'," & planyearS2 & ",'" & cmmt_codeS2 & "'," & planyearS2 & ",'" & FundedProgramS2 & "','" & FunctionalAreaS2 & "'," & IIf(product_codeS2 = "NULL", "NULL", "'" & product_codeS2 & "'") & "," & IIf(product_codeS2 = "NULL", "NULL", planyearS2) & "," & IIf(activity_type_codeS2 = "NULL", "NULL", "'" & activity_type_codeS2 & "'") & "," & IIf(activity_type_codeS2 = "NULL", "NULL", planyearS2) & "," & amount1S2 & "," & amount2S2 & "," & amount3S2 & "," & amount4S2 & "," & amount5S2 & "," & amount6S2 & "," & amount7S2 & "," & amount8S2 & "," & amount9S2 & "," & amount10S2 & "," & amount11S2 & "," & amount12S2 & ",GETDATE(), '" & username & "', " & BudgetDetail_Id & ")"
                                    Dim r As Integer = bd12_adapter.Execute(sqlBudget12)
                                    logger.Info("User " & Session("users") & " import actual from HR with total amount " & linecountS2.ToString)
                                End If

                            Catch ex As Exception
                                'oTrans.Rollback()

                                'logger.Error(lineS2.ToString)
                                'logger.Error(ex.Message)
                                'logger.Error(ex.StackTrace)

                                'Dim err As String = ex.Message.ToString().Replace("""", "'").Replace("'", "").Replace(Chr(13), "").Replace(",", "").Replace("/", "").Replace("<br>", "\\n").Replace("\\n", "").Replace(System.Environment.NewLine, "").Replace("\r", "").Replace(vbCr, "").Replace(vbLf, "")

                                Throw ex
                                'Alert("มีข้อผิดพลาดในการนำเข้าข้อมูล" + " " + err)
                                Exit Sub
                            End Try
                        End If
                        lineS2 = streaderS2.ReadLine()

                    End While
                End If
            End If
        Catch ex As Exception 'Have error 
            oTrans.Rollback()
            logger.Error(ex.Message)
            logger.Error(ex.StackTrace)

            Dim err As String = ex.Message.ToString().Replace("""", "'").Replace("'", "").Replace(Chr(13), "").Replace(",", "").Replace("/", "").Replace("<br>", "\\n").Replace("\\n", "").Replace(System.Environment.NewLine, "").Replace("\r", "").Replace(vbCr, "").Replace(vbLf, "")

            Alert("มีข้อผิดพลาดในการนำเข้าข้อมูล" + "\r\n" + err)
            'Throw ex
            Exit Sub
        End Try

        '--------------------------------------------------------------------------------------
        'While (line <> Nothing)

        '    line = streader.ReadLine()
        '    linecount += 1
        '    If linecount = 1 And delimeter <> "|" Then
        '        Continue While
        '    End If
        '    'DataArray = Split(FileArray(idx), delimeter)
        '    DataArray = Split(line, delimeter)
        '    If WebCombo1.Value = "1" Then
        '        If DataArray.Length > 2 Then

        '            planyear = PlanYearsTxt.Text
        '            fct_code = Right(DataArray(2), 4)
        '            cmmt_code = Right(DataArray(3).Trim, 8)
        '            FundedProgram = ""
        '            FunctionalArea = DataArray(5)
        '            activity_type_code = "NULL"
        '            product_code = "NULL"
        '            If Len(FunctionalArea) = 8 Then
        '                product_code = FunctionalArea
        '            ElseIf Len(FunctionalArea) = 6 Then
        '                activity_type_code = FunctionalArea
        '            End If
        '            FunctionalArea = ""
        '            period = Val(DataArray(6))
        '            data1 = Val(DataArray(7))
        '            amount = Val(Replace(DataArray(8), ",", ""))
        '            Try
        '                master_exist = ex_adapter.CheckFct(fct_code, Val(PlanYearsTxt.Text))
        '                If master_exist = 0 Then
        '                    If CheckBox1.Checked = True Then
        '                        InsertFct(fct_code, planyear)
        '                    Else
        '                        Alert("พบปัญหาบรรทัดที่ " & linecount & " Fund Center :" & fct_code & " ไม่มีอยู่ใน Master ")
        '                        Throw New ArgumentException("Master Error")
        '                    End If
        '                End If

        '                If activity_type_code <> "NULL" Then
        '                    master_exist = ex_adapter.CheckActType(activity_type_code, Val(PlanYearsTxt.Text))
        '                    If master_exist = 0 Then
        '                        If CheckBox1.Checked = True Then
        '                            InsertActivityType(activity_type_code, planyear)
        '                        Else
        '                            Alert("พบปัญหาบรรทัดที่ " & linecount & " Activity Type :" & activity_type_code & " ไม่มีอยู่ใน Master ")
        '                            Throw New ArgumentException("Master Error")
        '                        End If
        '                    End If
        '                    activity_type_code = "'" & activity_type_code & "'"
        '                End If

        '                If product_code <> "NULL" Then
        '                    master_exist = ex_adapter.CheckProduct(product_code, Val(PlanYearsTxt.Text))
        '                    If master_exist = 0 Then
        '                        If CheckBox1.Checked = True Then
        '                            InsertProduct(product_code, planyear)
        '                        Else
        '                            Alert("พบปัญหาบรรทัดที่ " & linecount & " Product:" & product_code & " ไม่มีอยู่ใน Master ")
        '                            Throw New ArgumentException("Master Error")
        '                        End If
        '                    End If
        '                    product_code = "'" & product_code & "'"
        '                End If

        '                master_exist = ex_adapter.CheckCmmt(cmmt_code, Val(PlanYearsTxt.Text))
        '                If master_exist = 0 Then
        '                    If CheckBox1.Checked = True Then
        '                        InsertCmmt(cmmt_code, planyear)
        '                    Else
        '                        Alert("พบปัญหาบรรทัดที่ " & linecount & " Commitment: " & cmmt_code & " ไม่มีอยู่ใน Master ")
        '                        Throw New ArgumentException("Master Error")
        '                    End If
        '                End If

        '                Dim totalAmount As Decimal = amount
        '                exist = bd_adapter.CheckExist1(planyear, version, fct_code, cmmt_code, FundedProgram, FunctionalArea, product_code, activity_type_code, data1)
        '                If exist = 0 Then
        '                    sql = "insert into budgetdetails (planyear , baseyear , version, FundCenter_FundCenterCode,FundCenter_PlanYear,Cmmt_CmmtCode,Cmmt_PlanYear,FundedProgram,FunctionalArea,Product_ProductCode,Product_PlanYear,ActivityType_ActivityTypeCode,ActivityType_PlanYear,Element3,Actual" & period & ",OldAssetNo, Amount)"
        '                    sql += " values ( " & planyear & "," & planyear & ",'" & version & "','" & fct_code & "'," & planyear & ",'" & cmmt_code & "'," & planyear & ",'" & FundedProgram & "','" & FunctionalArea & "'," & product_code & "," & planyear & "," & activity_type_code & "," & planyear & "," & data1 & "," & amount & "," & "''" & "," & totalAmount & ")"
        '                Else
        '                    sql = "update budgetdetails set Actual" & period & " = " & amount
        '                    sql += ", Amount = " & totalAmount
        '                    sql += " where planyear = " & planyear
        '                    sql += " and version = '" & version & "' "
        '                    sql += " and FundCenter_FundCenterCode = '" & fct_code & "'"
        '                    sql += " and Cmmt_CmmtCode = '" & cmmt_code & "'"
        '                    sql += " and FundedProgram = '" & FundedProgram & "'"
        '                    sql += " and Product_ProductCode = '" & product_code & "'"
        '                    sql += " and ActivityType_ActivityTypeCode = '" & activity_type_code & "'"

        '                End If
        '                bd_adapter.Execute(sql)
        '            Catch ex As Exception
        '                oTrans.Rollback()

        '                logger.Error(line.ToString)
        '                logger.Error(ex.Message)
        '                logger.Error(ex.StackTrace)

        '                Alert("มีข้อผิดพลาดในการนำเข้าข้อมูล")
        '                Exit Sub
        '            End Try
        '        End If

        '        logger.Info("User " & Session("users") & " import actual from FM with total amount " & linecount.ToString)
        '    ElseIf WebCombo1.Value = "2" Then

        '        If DataArray.Length > 2 Then
        '            planyear = Val(PlanYearsTxt.Text)
        '            FundedProgram = ""
        '            cmmt_code = Right(DataArray(1).Trim, 8)
        '            If DataArray(2) = "H" Then
        '                data1 = 0
        '            Else
        '                data1 = 1
        '            End If

        '            fct_code = Right(DataArray(3).Trim, 4)
        '            FunctionalArea = DataArray(4).Trim
        '            activity_type_code = "NULL"
        '            product_code = "NULL"
        '            If Len(FunctionalArea) = 8 Then
        '                product_code = FunctionalArea
        '            ElseIf Len(FunctionalArea) = 6 Then
        '                activity_type_code = FunctionalArea
        '            End If
        '            FunctionalArea = ""
        '            data2 = Val(Replace(DataArray(5).Trim, ",", ""))
        '            amount1 = Val(Replace(DataArray(6).Trim, ",", ""))
        '            amount2 = Val(Replace(DataArray(7).Trim, ",", ""))
        '            amount3 = Val(Replace(DataArray(8).Trim, ",", ""))
        '            amount4 = Val(Replace(DataArray(9).Trim, ",", ""))
        '            amount5 = Val(Replace(DataArray(10).Trim, ",", ""))
        '            amount6 = Val(Replace(DataArray(11).Trim, ",", ""))
        '            amount7 = Val(Replace(DataArray(12).Trim, ",", ""))
        '            amount8 = Val(Replace(DataArray(13).Trim, ",", ""))
        '            amount9 = Val(Replace(DataArray(14).Trim, ",", ""))
        '            amount10 = Val(Replace(DataArray(15).Trim, ",", ""))
        '            amount11 = Val(Replace(DataArray(16).Trim, ",", ""))
        '            amount12 = Val(Replace(DataArray(17).Trim, ",", ""))
        '            Try
        '                master_exist = ex_adapter.CheckFct(fct_code, Val(PlanYearsTxt.Text))
        '                If master_exist = 0 Then
        '                    If CheckBox1.Checked = True Then
        '                        InsertFct(fct_code, planyear)
        '                    Else
        '                        Alert("พบปัญหาบรรทัดที่ " & linecount & " Fund Center:" & fct_code & " ไม่มีอยู่ใน Master ")
        '                        Throw New ArgumentException("Master Error")
        '                    End If
        '                End If

        '                If activity_type_code <> "NULL" Then
        '                    master_exist = ex_adapter.CheckActType(activity_type_code, Val(PlanYearsTxt.Text))
        '                    If master_exist = 0 Then
        '                        If CheckBox1.Checked = True Then
        '                            InsertActivityType(activity_type_code, planyear)
        '                        Else
        '                            Alert("พบปัญหาบรรทัดที่ " & linecount & " Activity Type :" & activity_type_code & " ไม่มีอยู่ใน Master ")
        '                            Throw New ArgumentException("Master Error")
        '                        End If
        '                    End If
        '                    activity_type_code = "'" & activity_type_code & "'"
        '                End If

        '                If product_code <> "NULL" Then
        '                    master_exist = ex_adapter.CheckProduct(product_code, Val(PlanYearsTxt.Text))
        '                    If master_exist = 0 Then
        '                        If CheckBox1.Checked = True Then
        '                            InsertProduct(product_code, planyear)
        '                        Else
        '                            Alert("พบปัญหาบรรทัดที่ " & linecount & " Product:" & product_code & " ไม่มีอยู่ใน Master ")
        '                            Throw New ArgumentException("Master Error")
        '                        End If
        '                    End If
        '                    product_code = "'" & product_code & "'"
        '                End If

        '                master_exist = ex_adapter.CheckCmmt(cmmt_code, Val(PlanYearsTxt.Text))
        '                If master_exist = 0 Then
        '                    If CheckBox1.Checked = True Then
        '                        InsertCmmt(cmmt_code, planyear)
        '                    Else
        '                        Alert("พบปัญหาบรรทัดที่ " & linecount & " Commitment: " & cmmt_code & " ไม่มีอยู่ใน Master ")
        '                        Throw New ArgumentException("Master Error")
        '                    End If
        '                End If

        '                Dim totalAmount As Decimal = amount1 + amount2 + amount3 + amount4 + amount5 + amount6 + amount7 + amount8 + amount9 + amount10 + amount11 + amount12
        '                sql = "insert into budgetdetails (planyear , baseyear , version, FundCenter_FundCenterCode,FundCenter_PlanYear,Cmmt_CmmtCode,Cmmt_PlanYear,FundedProgram,FunctionalArea,Product_ProductCode,Product_PlanYear,ActivityType_ActivityTypeCode,ActivityType_PlanYear,Element1,Element2,Actual1,Actual2,Actual3,Actual4,Actual5,Actual6,Actual7,Actual8,Actual9,Actual10,Actual11,Actual12,OldAssetNo, Amount)"
        '                sql += " values ( " & planyear & "," & planyear & ",'" & version & "','" & fct_code & "'," & planyear & ",'" & cmmt_code & "'," & planyear & ",'" & FundedProgram & "','" & FunctionalArea & "'," & product_code & "," & planyear & "," & activity_type_code & "," & planyear & "," & data1 & "," & data2 & "," & amount1 & "," & amount2 & "," & amount3 & "," & amount4 & "," & amount5 & "," & amount6 & "," & amount7 & "," & amount8 & "," & amount9 & "," & amount10 & "," & amount11 & "," & amount12 & "," & "''" & "," & totalAmount & ")"

        '                bd_adapter.Execute(sql)
        '            Catch ex As Exception
        '                oTrans.Rollback()

        '                logger.Error(line.ToString)
        '                logger.Error(ex.Message)
        '                logger.Error(ex.StackTrace)

        '                'Response.Write(fct_code & "#" & ex.Message)
        '                Alert("มีข้อผิดพลาดในการนำเข้าข้อมูล")
        '                Exit Sub
        '            End Try

        '            logger.Info("User " & Session("users") & " import actual from non-FM with total amount " & linecount.ToString)
        '        End If
        '    ElseIf WebCombo1.Value = "3" Then
        '        If DataArray.Length > 2 Then
        '            planyear = PlanYearsTxt.Text
        '            FundedProgram = ""
        '            fund_code = DataArray(1).Trim
        '            fct_code = Right(DataArray(2).Trim, 4)
        '            cmmt_code = Right(DataArray(3).Trim, 8)
        '            FundedProgram = DataArray(4).Trim
        '            If FundedProgram = "OPEX" Then
        '                FundedProgram = ""
        '            End If

        '            FunctionalArea = DataArray(5).Trim
        '            activity_type_code = "NULL"
        '            product_code = "NULL"
        '            If Len(FunctionalArea) = 8 Then
        '                product_code = FunctionalArea
        '            ElseIf Len(FunctionalArea) = 6 Then
        '                activity_type_code = FunctionalArea
        '            End If
        '            FunctionalArea = ""
        '            amount1 = Val(Replace(DataArray(6).Trim, ",", ""))
        '            amount2 = Val(Replace(DataArray(7).Trim, ",", ""))
        '            amount3 = Val(Replace(DataArray(8).Trim, ",", ""))
        '            amount4 = Val(Replace(DataArray(9).Trim, ",", ""))
        '            amount5 = Val(Replace(DataArray(10).Trim, ",", ""))
        '            amount6 = Val(Replace(DataArray(11).Trim, ",", ""))
        '            amount7 = Val(Replace(DataArray(12).Trim, ",", ""))
        '            amount8 = Val(Replace(DataArray(13).Trim, ",", ""))
        '            amount9 = Val(Replace(DataArray(14).Trim, ",", ""))
        '            amount10 = Val(Replace(DataArray(15).Trim, ",", ""))
        '            amount11 = Val(Replace(DataArray(16).Trim, ",", ""))
        '            amount12 = Val(Replace(DataArray(17).Trim, ",", ""))
        '            Try
        '                master_exist = ex_adapter.CheckFct(fct_code, Val(PlanYearsTxt.Text))
        '                If master_exist = 0 Then
        '                    If CheckBox1.Checked = True Then
        '                        InsertFct(fct_code, planyear)
        '                    Else
        '                        Alert("พบปัญหาบรรทัดที่ " & linecount & " Fund Center:" & fct_code & " ไม่มีอยู่ใน Master ")
        '                        Throw New ArgumentException("Master Error")
        '                    End If
        '                End If

        '                If activity_type_code <> "NULL" Then
        '                    master_exist = ex_adapter.CheckActType(activity_type_code, Val(PlanYearsTxt.Text))
        '                    If master_exist = 0 Then
        '                        If CheckBox1.Checked = True Then
        '                            InsertActivityType(activity_type_code, planyear)
        '                        Else
        '                            Alert("พบปัญหาบรรทัดที่ " & linecount & " Activity Type :" & activity_type_code & " ไม่มีอยู่ใน Master ")
        '                            Throw New ArgumentException("Master Error")
        '                        End If
        '                    End If
        '                    activity_type_code = "'" & activity_type_code & "'"
        '                End If

        '                If product_code <> "NULL" Then
        '                    master_exist = ex_adapter.CheckProduct(product_code, Val(PlanYearsTxt.Text))
        '                    If master_exist = 0 Then
        '                        If CheckBox1.Checked = True Then
        '                            InsertProduct(product_code, planyear)
        '                        Else
        '                            Alert("พบปัญหาบรรทัดที่ " & linecount & " Product:" & product_code & " ไม่มีอยู่ใน Master ")
        '                            Throw New ArgumentException("Master Error")
        '                        End If
        '                    End If
        '                    product_code = "'" & product_code & "'"
        '                End If

        '                master_exist = ex_adapter.CheckCmmt(cmmt_code, Val(PlanYearsTxt.Text))
        '                If master_exist = 0 Then
        '                    If CheckBox1.Checked = True Then
        '                        InsertCmmt(cmmt_code, planyear)
        '                    Else
        '                        Alert("พบปัญหาบรรทัดที่ " & linecount & " Commitment: " & cmmt_code & " ไม่มีอยู่ใน Master ")
        '                        Throw New ArgumentException("Master Error")
        '                    End If
        '                End If

        '                Dim totalAmount As Decimal = amount1 + amount2 + amount3 + amount4 + amount5 + amount6 + amount7 + amount8 + amount9 + amount10 + amount11 + amount12
        '                sql = "insert into budgetdetails (planyear , baseyear , version, FundCenter_FundCenterCode,FundCenter_PlanYear,Cmmt_CmmtCode,Cmmt_PlanYear,FundedProgram,FunctionalArea,Product_ProductCode,Product_PlanYear,ActivityType_ActivityTypeCode,ActivityType_PlanYear,Actual1,Actual2,Actual3,Actual4,Actual5,Actual6,Actual7,Actual8,Actual9,Actual10,Actual11,Actual12,OldAssetNo, Amount)"
        '                sql += " values ( " & planyear & "," & planyear & ",'" & version & "','" & fct_code & "'," & planyear & ",'" & cmmt_code & "'," & planyear & ",'" & FundedProgram & "','" & FunctionalArea & "'," & product_code & "," & planyear & "," & activity_type_code & "," & planyear & "," & amount1 & "," & amount2 & "," & amount3 & "," & amount4 & "," & amount5 & "," & amount6 & "," & amount7 & "," & amount8 & "," & amount9 & "," & amount10 & "," & amount11 & "," & amount12 & "," & "''" & "," & totalAmount & ")"
        '                'Response.Write(sql & "<br />")
        '                bd_adapter.Execute(sql)

        '                logger.Info("User " & Session("users") & " import actual from P&L with total amount " & linecount.ToString)
        '            Catch ex As Exception
        '                oTrans.Rollback()

        '                logger.Error(line.ToString)
        '                logger.Error(ex.Message)
        '                logger.Error(ex.StackTrace)

        '                Alert("มีข้อผิดพลาดในการนำเข้าข้อมูล")
        '                Exit Sub
        '            End Try
        '        End If
        '    ElseIf WebCombo1.Value = "4" Then
        '        If DataArray.Length > 2 Then
        '            planyear = PlanYearsTxt.Text
        '            FundedProgram = ""
        '            fund_code = DataArray(1).Trim
        '            fct_code = Right(DataArray(2).Trim, 4)
        '            cmmt_code = Right(DataArray(3).Trim, 8)
        '            FundedProgram = DataArray(4).Trim
        '            If FundedProgram = "OPEX" Then
        '                FundedProgram = ""
        '            End If

        '            FunctionalArea = DataArray(5).Trim
        '            activity_type_code = "NULL"
        '            product_code = "NULL"
        '            If Len(FunctionalArea) = 8 Then
        '                product_code = FunctionalArea
        '            ElseIf Len(FunctionalArea) = 6 Then
        '                activity_type_code = FunctionalArea
        '            End If
        '            FunctionalArea = ""
        '            amount1 = Val(Replace(DataArray(6).Trim, ",", ""))
        '            amount2 = Val(Replace(DataArray(7).Trim, ",", ""))
        '            amount3 = Val(Replace(DataArray(8).Trim, ",", ""))
        '            amount4 = Val(Replace(DataArray(9).Trim, ",", ""))
        '            amount5 = Val(Replace(DataArray(10).Trim, ",", ""))
        '            amount6 = Val(Replace(DataArray(11).Trim, ",", ""))
        '            amount7 = Val(Replace(DataArray(12).Trim, ",", ""))
        '            amount8 = Val(Replace(DataArray(13).Trim, ",", ""))
        '            amount9 = Val(Replace(DataArray(14).Trim, ",", ""))
        '            amount10 = Val(Replace(DataArray(15).Trim, ",", ""))
        '            amount11 = Val(Replace(DataArray(16).Trim, ",", ""))
        '            amount12 = Val(Replace(DataArray(17).Trim, ",", ""))
        '            Try
        '                master_exist = ex_adapter.CheckFct(fct_code, Val(PlanYearsTxt.Text))
        '                If master_exist = 0 Then
        '                    If CheckBox1.Checked = True Then
        '                        InsertFct(fct_code, planyear)
        '                    Else
        '                        Alert("พบปัญหาบรรทัดที่ " & linecount & " Fund Center :" & fct_code & " ไม่มีอยู่ใน Master ")
        '                        Throw New ArgumentException("Master Error")
        '                    End If
        '                End If

        '                If activity_type_code <> "NULL" Then
        '                    master_exist = ex_adapter.CheckActType(activity_type_code, Val(PlanYearsTxt.Text))
        '                    If master_exist = 0 Then
        '                        If CheckBox1.Checked = True Then
        '                            InsertActivityType(activity_type_code, planyear)
        '                        Else
        '                            Alert("พบปัญหาบรรทัดที่ " & linecount & " Activity Type :" & activity_type_code & " ไม่มีอยู่ใน Master ")
        '                            Throw New ArgumentException("Master Error")
        '                        End If
        '                    End If
        '                    activity_type_code = "'" & activity_type_code & "'"
        '                End If

        '                If product_code <> "NULL" Then
        '                    master_exist = ex_adapter.CheckProduct(product_code, Val(PlanYearsTxt.Text))
        '                    If master_exist = 0 Then
        '                        If CheckBox1.Checked = True Then
        '                            InsertProduct(product_code, planyear)
        '                        Else
        '                            Alert("พบปัญหาบรรทัดที่ " & linecount & " Product:" & product_code & " ไม่มีอยู่ใน Master ")
        '                            Throw New ArgumentException("Master Error")
        '                        End If
        '                    End If
        '                    product_code = "'" & product_code & "'"
        '                End If

        '                master_exist = ex_adapter.CheckCmmt(cmmt_code, Val(PlanYearsTxt.Text))
        '                If master_exist = 0 Then
        '                    If CheckBox1.Checked = True Then
        '                        InsertCmmt(cmmt_code, planyear)
        '                    Else
        '                        Alert("พบปัญหาบรรทัดที่ " & linecount & " Commitment: " & cmmt_code & " ไม่มีอยู่ใน Master ")
        '                        Throw New ArgumentException("Master Error")
        '                    End If
        '                End If

        '                Dim totalAmount As Decimal = amount1 + amount2 + amount3 + amount4 + amount5 + amount6 + amount7 + amount8 + amount9 + amount10 + amount11 + amount12

        '                sql = "insert into budgetdetails (planyear , baseyear , version, FundCenter_FundCenterCode,FundCenter_PlanYear,Cmmt_CmmtCode,Cmmt_PlanYear,FundedProgram,FunctionalArea,Product_ProductCode,Product_PlanYear,ActivityType_ActivityTypeCode,ActivityType_PlanYear,Actual1,Actual2,Actual3,Actual4,Actual5,Actual6,Actual7,Actual8,Actual9,Actual10,Actual11,Actual12,OldAssetNo, Amount)"
        '                sql += " values ( " & planyear & "," & planyear & ",'" & version & "','" & fct_code & "'," & planyear & ",'" & cmmt_code & "'," & planyear & ",'" & FundedProgram & "','" & FunctionalArea & "'," & product_code & "," & planyear & "," & activity_type_code & "," & planyear & "," & amount1 & "," & amount2 & "," & amount3 & "," & amount4 & "," & amount5 & "," & amount6 & "," & amount7 & "," & amount8 & "," & amount9 & "," & amount10 & "," & amount11 & "," & amount12 & "," & "''" & "," & totalAmount & ")"
        '                'Response.Write(sql)
        '                bd_adapter.Execute(sql)

        '                logger.Info("User " & Session("users") & " import actual from HR with total amount " & linecount.ToString)
        '            Catch ex As Exception
        '                oTrans.Rollback()

        '                logger.Error(line.ToString)
        '                logger.Error(ex.Message)
        '                logger.Error(ex.StackTrace)

        '                Alert("มีข้อผิดพลาดในการนำเข้าข้อมูล")
        '                Exit Sub
        '            End Try
        '        End If
        '    End If
        'End While

        'Next

        'Save Success
        oTrans.Commit()
        endtime = Now

        ' ''Get Last insert data 
        ''Dim BD_adap As New SplitData2TableAdapters.BudgetDetailsTableAdapter
        ''Dim BD_dt As New SplitData2.BudgetDetailsDataTable
        ''Dim adapter12 As New MassChangeDSTableAdapters.Budget12TableAdapter()
        ''Dim selectBDId As String = ""
        ''Dim oBD12_Trans = adapter12.CreateTransaction()

        ''selectBDId = "SELECT TOP " & linecount & " * FROM BudgetDetails ORDER BY Id DESC"
        ''BD_adap.GetDataByFilter(BD_dt, selectBDId)

        ''For Each irow As SplitData2.BudgetDetailsRow In BD_dt
        ''    sqlS2 = "insert into budgetdetails (planyear , baseyear , version, FundCenter_FundCenterCode,FundCenter_PlanYear,Cmmt_CmmtCode,Cmmt_PlanYear,FundedProgram,FunctionalArea,Product_ProductCode,Product_PlanYear,ActivityType_ActivityTypeCode,ActivityType_PlanYear,Actual1,Actual2,Actual3,Actual4,Actual5,Actual6,Actual7,Actual8,Actual9,Actual10,Actual11,Actual12,CreatedDate,OldAssetNo, Amount)"
        ''    sqlS2 += " values ( " & planyearS2 & "," & planyearS2 & ",'" & versionS2 & "','" & fct_codeS2 & "'," & planyearS2 & ",'" & cmmt_codeS2 & "'," & planyearS2 & ",'" & FundedProgramS2 & "','" & FunctionalAreaS2 & "'," & IIf(product_codeS2 = "NULL", "NULL", "'" & product_codeS2 & "'") & "," & IIf(product_codeS2 = "NULL", "NULL", planyearS2) & "," & IIf(activity_type_codeS2 = "NULL", "NULL", "'" & activity_type_codeS2 & "'") & "," & IIf(activity_type_codeS2 = "NULL", "NULL", planyearS2) & "," & amount1S2 & "," & amount2S2 & "," & amount3S2 & "," & amount4S2 & "," & amount5S2 & "," & amount6S2 & "," & amount7S2 & "," & amount8S2 & "," & amount9S2 & "," & amount10S2 & "," & amount11S2 & "," & amount12S2 & ",GETDATE()," & "''" & "," & totalAmount & ")"
        ''    'Response.Write(sql)
        ''    budgetDetailTA.SetCommandTimeOut(ConfigurationManager.AppSettings("QueryTimeOut"))

        ''    bd_adapter.Execute(sqlS2)

        ''    logger.Info("User " & Session("users") & " import actual from HR with total amount " & linecountS2.ToString)
        ''Next
        ''oBD12_Trans.Commit()

        'Response.Write(starttime & "#" & endtime)
        Alert("เสร็จสิ้นการนำเข้าข้อมูล จำนวนรายการทั้งหมดคือ " & linecount & " รายการ")
    End Sub

    Sub InsertFct(fctCode As String, planyear As Decimal)
        Dim bd_adapter As New MasschangeDSTableAdapters.BudgetDetailsTableAdapter
        Dim sql As String
        sql = "insert into fundcenters (fundcentercode , planyear) values ('" & fctCode & "'," & planyear & ")"
        bd_adapter.Execute(sql)
    End Sub

    Sub InsertCmmt(CmmtCode As String, planyear As Decimal)
        Dim bd_adapter As New MasschangeDSTableAdapters.BudgetDetailsTableAdapter
        Dim sql As String
        sql = "insert into Cmmts (CmmtCode , planyear) values ('" & CmmtCode & "'," & planyear & ")"
        bd_adapter.Execute(sql)
    End Sub

    Sub InsertProduct(ProductCode As String, planyear As Decimal)
        Dim bd_adapter As New MasschangeDSTableAdapters.BudgetDetailsTableAdapter
        Dim sql As String
        sql = "insert into Products (ProductCode , planyear) values ('" & ProductCode & "'," & planyear & ")"
        bd_adapter.Execute(sql)
    End Sub

    Sub InsertActivityType(ActivityTypeCode As String, planyear As Decimal)
        Dim bd_adapter As New MasschangeDSTableAdapters.BudgetDetailsTableAdapter
        Dim sql As String
        sql = "insert into ActivityTypes (ActivityTypeCode , planyear, Description, Price, AttachmentFlag) values ('" & ActivityTypeCode & "'," & planyear & ",'',0,'N')"
        bd_adapter.Execute(sql)
    End Sub

    Function GenerateFileName(ByVal data As String) As String
        Return Now().ToString(data)
    End Function
    Sub Alert(ByVal message As String)
        Try
            AlertText += "<script> alert(""" & message & """);</script>"
        Catch ex As Exception

        End Try
        
    End Sub

    Private Function chkFileName(filename As String) As Boolean
        If WebCombo1.Value = "1" Then
            Dim rgx As New Regex("^(ZFMI_)+\d{3}(BB_)+(" & PlanYearsTxt.Text & ")")
            Return rgx.IsMatch(filename)
        ElseIf WebCombo1.Value = "2" Then
            Dim rgx As New Regex("^(Interface_Non FM to )" + "(BB_" & PlanYearsTxt.Text & ")")
            Return rgx.IsMatch(filename)
        ElseIf WebCombo1.Value = "3" Then
            Dim rgx As New Regex("^(ZFMI_)+\d{3}(BB_)+(" & PlanYearsTxt.Text & ")")
            Return rgx.IsMatch(filename)
        ElseIf WebCombo1.Value = "4" Then
            Dim rgx As New Regex("^(HR)+(BB_)+(" & PlanYearsTxt.Text & ")")
            Return rgx.IsMatch(filename)
        Else
            Return False
        End If

    End Function
    Public Function SpacePrefix(strParam As String, nLen As Integer) As String
        Dim tmp As String
        Dim k As Integer
        tmp = LTrim(RTrim(strParam))
        If Len(tmp) < nLen Then
            For k = 1 To nLen - 1
                tmp = "0" & tmp
            Next k
        End If
        SpacePrefix = tmp
    End Function

    Private Sub downloadErr(txtlog As String)
        Dim strFileName As String = System.IO.Path.GetRandomFileName()
        Dim strName As String = "Import_Error_Log_" & strFileName & ".txt"

        Using sw As New System.IO.StreamWriter(Server.MapPath("~/Import_Error_Log/" + strName + ".txt"))
            sw.WriteLine(txtlog)
            sw.Close()
        End Using

        Response.ContentType = "text/plain"
        Dim FilePath As String = MapPath("~/Import_Error_Log/" + strName + ".txt")
        DownloadFile(strName, True, FilePath, ".txt")
        Response.End()
    End Sub
    Private Sub DownloadFile(ByVal fname As String, ByVal forceDownload As Boolean, path As String, ext As String)
        ' Dim path As Path
        Dim type As String = ""

        Select Case ext
            Case ".htm", ".html"
                type = "text/HTML"
            Case ".txt"
                type = "text/plain"
            Case ".doc", ".rtf"
                type = "Application/msword"
            Case ".csv", ".xls"
                type = "Application/x-msexcel"
            Case Else
                type = "text/plain"
        End Select

        If (forceDownload) Then
            Response.AppendHeader("content-disposition", _
            "attachment; filename=" + fname)
        End If
        If type <> "" Then
            Response.ContentType = type
        End If

        Response.WriteFile(path)


    End Sub

    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        httpDownload(hd_err_txt.Value)
    End Sub

    Sub httpDownload(ByVal txtlog As String)
        Dim success As Boolean = False

        Try
            Response.Clear()
            Response.AddHeader("content-disposition", "attachment;filename=Import_log_error.txt")
            Response.Buffer = True
            Response.Charset = "UTF-8"
            'set encoding as Unicode due to cannot read Thai characters on SAP system
            Response.ContentEncoding = Encoding.Unicode
            'Response.Cache.SetCacheability(HttpCacheability.NoCache)
            Response.ContentType = "application/vnd.text"

            Dim stringWrite As New System.IO.StringWriter()
            Dim htmlWrite = New HtmlTextWriter(stringWrite)

            Response.Write(txtlog)
            success = True

            logger.Info("User " & Session("users") & " download file successfully.")
            ' Response.End()
        Catch ex As Exception
            logger.Error(ex.Message)
            logger.Error(ex.StackTrace)

            success = False

            logger.Info("User " & Session("users") & " download file failed.")
        End Try
        Response.End()

    End Sub
End Class
